import 'package:flutter/foundation.dart';
import 'package:edu/utils/account_info.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqlbrite/sqlbrite.dart';
import 'package:synchronized/synchronized.dart';

/*
 * 数据库帮助类
 */
class DatabaseHelper {
  static const _kDatabaseName = 'sqlite.db'; // 数据库名称
  static const _kDatabaseVersion = 1; // 数据库版本
  static const _kSearchHistoryTable = 'search_history'; // 搜索历史表
  static const _kVideoProgressTable = 'video_progress'; // 视频播放进度表
  static const _kKeyValueStoreTable = 'key_value_store'; // 键值存储表

  static late BriteDatabase _streamDatabase; // 数据库对象

  DatabaseHelper._();
  static final shared = DatabaseHelper._();

  static var lock = Lock();

  static Database? _databse;

  Future<void> init() async {
    await database;
  }

  // 创建表
  Future _onCreate(Database db, int verion) async {
    // 创建搜索历史表
    await db.execute('''
          CREATE TABLE $_kSearchHistoryTable (
            user_id INTEGER NOT NULL,
            search_text TEXT NOT NULL,
            search_time DATETIME DEFAULT (datetime('now')),
            PRIMARY KEY (user_id, search_text)
          )
      ''');
    // 创建视频播放进度表
    await db.execute('''
          CREATE TABLE $_kVideoProgressTable (
            user_id INTEGER NOT NULL,
            video_id INTEGER NOT NULL,
            course_id INTEGER NOT NULL,
            duration INTEGER NOT NULL,
            progress REAL NOT NULL,
            PRIMARY KEY (user_id, video_id)
          )
      ''');
    // 创建键值存储表
    await db.execute('''
          CREATE TABLE $_kKeyValueStoreTable (
            user_id INTEGER NOT NULL,
            key TEXT NOT NULL,
            string_value TEXT,
            integer_value INTEGER,
            real_value REAL,
            PRIMARY KEY (user_id, key)
          )
      ''');
  }

  // 初始化数据库
  Future<Database> _initDatabase() async {
    final documentDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentDirectory.path, _kDatabaseName);

    // ignore: deprecated_member_use
    Sqflite.devSetDebugModeOn(kDebugMode);

    return openDatabase(path, version: _kDatabaseVersion, onCreate: _onCreate);
  }

  // 获取数据库对象
  Future<Database> get database async {
    if (_databse != null) {
      return _databse!;
    }

    await lock.synchronized(() async {
      if (_databse == null) {
        _databse = await _initDatabase();
        _streamDatabase = BriteDatabase(_databse!);
      }
    });

    return _databse!;
  }

  Future<BriteDatabase> get streamDatabase async {
    await database;
    return _streamDatabase;
  }

  // 插入搜索记录
  Future<void> insertSearchText(String text) async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return;
    }

    await _streamDatabase.insert(_kSearchHistoryTable, {
      'user_id': uid,
      'search_text': text,
    }, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  // 获取搜索记录
  Future<List<String>> getSearchHistories() async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return [];
    }
    final rows = await _streamDatabase.query(
      _kSearchHistoryTable,
      where: 'user_id = ?',
      whereArgs: [uid],
      orderBy: 'search_time DESC',
      limit: 10,
    );
    final results = <String>[];
    for (final row in rows) {
      results.add(row['search_text'] as String);
    }
    return results;
  }

  // 清空搜索记录
  Future<void> clearSearchHistories() async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return;
    }
    await _streamDatabase.delete(
      _kSearchHistoryTable,
      where: 'user_id = ?',
      whereArgs: [uid],
    );
  }

  // 插入视频播放进度
  Future<void> insertVideoProgress(
    int videoId,
    int courseId,
    int duration,
    double progress,
  ) async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return;
    }

    if (progress == double.infinity || progress.isNaN) {
      return;
    }

    await _streamDatabase.insert(_kVideoProgressTable, {
      'user_id': uid,
      'video_id': videoId,
      'course_id': courseId,
      'duration': duration,
      'progress': progress,
    }, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  // 获取教育课程时长
  Future<int?> getEduCourseDuration(int courseId) async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return null;
    }

    const sql =
        '''
          SELECT SUM(duration) AS duration 
          FROM $_kVideoProgressTable
          WHERE user_id = ? AND course_id = ?
        ''';

    final rows = await _streamDatabase.rawQuery(sql, [uid, courseId]);
    if (rows.isEmpty) {
      return null;
    } else {
      return rows.first['duration'] as int;
    }
  }

  // 获取视频播放进度
  Future<double?> getVideoProgress(int videoId) async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return null;
    }

    final rows = await _streamDatabase.query(
      _kVideoProgressTable,
      where: 'user_id = ? AND video_id = ?',
      whereArgs: [uid, videoId],
    );

    if (rows.isEmpty) {
      return null;
    } else {
      return rows.first['progress'] as double;
    }
  }

  // 插入键值对
  Future<void> setKeyValue(
    String key, {
    String? string,
    int? integer,
    double? real,
  }) async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return;
    }

    await _streamDatabase.insert(_kKeyValueStoreTable, {
      'user_id': uid,
      'key': key,
      'string_value': string,
      'integer_value': integer,
      'real_value': real,
    }, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  // 获取键值对 string
  Future<dynamic> getStringValue(String key) async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return null;
    }

    final rows = await _streamDatabase.query(
      _kKeyValueStoreTable,
      where: 'user_id = ? AND key = ?',
      whereArgs: [uid, key],
    );

    if (rows.isEmpty) {
      return null;
    } else {
      return rows.first['string_value'] as String;
    }
  }

  // 获取键值对 integer
  Future<int?> getIntegerValue(String key) async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return null;
    }

    final rows = await _streamDatabase.query(
      _kKeyValueStoreTable,
      where: 'user_id = ? AND key = ?',
      whereArgs: [uid, key],
    );

    if (rows.isEmpty) {
      return null;
    } else {
      return rows.first['integer_value'] as int;
    }
  }

  // 获取键值对 double
  Future<double?> getRealValue(String key) async {
    final uid = AccountInfo.shared.accountId;
    if (uid == null) {
      return null;
    }

    final rows = await _streamDatabase.query(
      _kKeyValueStoreTable,
      where: 'user_id = ? AND key = ?',
      whereArgs: [uid, key],
    );

    if (rows.isEmpty) {
      return null;
    } else {
      return rows.first['real_value'] as double;
    }
  }
}
